Session02 Additional Info

Cardinality

Cardinality in Database Relationships

Cardinality in databases refers to the number of relationships between records in two tables. It defines how many instances of one entity can be associated with instances of another entity. Cardinality is a crucial concept in database design because it ensures data integrity and optimizes query performance.

Types of Cardinality

One-to-One (1:1)
- Each record in Table A is related to exactly one record in Table B, and vice versa.
- Example: A person has one passport, and a passport belongs to only one person.
- Implementation: Typically enforced with a unique foreign key.
One-to-Many (1:M)
- A record in Table A can have multiple related records in Table B, but a record in Table B is linked to only one record in Table A.
- Example: A customer can place multiple orders, but each order is placed by only one customer.
- Implementation: A foreign key in Table B referring to the primary key in Table A.
Many-to-Many (M:M)
- Multiple records in Table A can relate to multiple records in Table B.
- Example: Students enroll in multiple courses, and each course has multiple students.
- Implementation: A junction (bridge) table with foreign keys referencing both tables.

Cardinality Constraints

Cardinality can be further specified using minimum and maximum constraints:

(0,1): Optional One → A record may or may not be related.
(1,1): Mandatory One → A record must always be related to exactly one record.
(0,N): Optional Many → A record may have many related records or none.
(1,N): Mandatory Many → A record must have at least one related record.

Practical Example

Consider a database with Students and Courses:

One-to-Many: A teacher teaches multiple courses, but each course has only one teacher.
Many-to-Many: Students enroll in multiple courses, and courses have multiple students. This is implemented using a StudentCourses junction table.

Would you like a more detailed example or SQL implementation? 🚀